from flaskr.db import get_db
import time

def game_get_all_info(id, request_username):
    '''
    id: 房间唯一标识id
    '''
    db = get_db()

    all_info = dict()
    
    # room中的数据全给他拿出来
    sql = "select * from room where id = ?;"
    db_data = db.execute(sql, (id, )).fetchone()
    if not db_data:
        return ('-1', '房间不存在')
    
    all_info.update({
        'id': id,
        'gamestatus': db_data['gamestatus'],
        'currentword': db_data['currentword'],
        'hoster': db_data['hoster'],
        'targettime': db_data['targettime'],
        'boarddata': db_data['boarddata']
    })

    # 所有玩家信息给他拿出来
    user_list = []
    current_user = dict()
    sql = '''
        select user.nickedname, user.headpicture, user.username, roomplayer.isready, roomplayer.drawingstatus, roomplayer.grade
        from user, roomplayer 
        where user.username = roomplayer.username and roomplayer.id = ?;
        '''
    user_data = db.execute(sql, (id, )).fetchall()
    for item in user_data:
        user_list.append({
            'headpicture': item['headpicture'],
            'nickedname': item['nickedname'],
            'username': item['username'],
            'isready': item['isready'],
            'drawingstatus': item['drawingstatus'],
            'grade': item['grade']
        })
        if item['username'] == request_username:
            current_user = user_list[-1]
    
    all_info['userlist'] = user_list
    all_info['current_user'] = current_user

    # 获取最近10条聊天记录
    sql = '''
            select roommessage.id, roommessage.username, message, nickedname 
            from roommessage, user 
            where tableid = ? and roommessage.username = user.username
            order by roommessage.id desc limit 10;'''
    messages = db.execute(sql, (id, )).fetchall()
    message_list = []
    for message in messages:
        message_list.append({
            "sendtime": message['id'],
            "nickedname": message['nickedname'],
            "message": message['message'],
            "username": message['username']
        })
    all_info['message_list'] = message_list

    return ('0', all_info)

def ready(username, isevent):
    db = get_db()
     # 用户是否在房间中？
    sql = "select room.id, room.gamestatus, roomplayer.isready from room, roomplayer where room.id = roomplayer.id and username = ?;"
    db_data = db.execute(sql, (username, )).fetchone()
    if not db_data:
        return ('-1', '用户未在房间中')
    if db_data['isready'] == 1 and db_data['gamestatus'] == 1:
        return ('-1', '游戏已经开始不可以取消准备')
    
    sql = "update roomplayer set isready = ? where username = ?;"
    db.execute(sql, (not db_data['isready'], username))
    db.commit()

    # 检查游戏状态
    check_game_status(db_data['id'])

    isevent[0] = True

    return ('0', '')

# 查看游戏状态
def look_game_status(id):
    db = get_db()
    sql = 'select gamestatus from room where id = ?'
    return db.execute(sql, (id, )).fetchone()['gamestatus']

def update_word(id, isevent):
    '''
    当游戏状态为1并且targettime为NULL or 现在的时间大于等于targettime的时候执行
    更新候选词，主持人，目标结束时间
    '''
    db = get_db()
    # 能执行到这个函数说明已经放在“游戏执行中”不需要再判断游戏状态
    sql = "select * from room where id = ?;"
    room_info = db.execute(sql, (id, )).fetchone()
    if not room_info['targettime'] or int(time.time() >= room_info['targettime']):
        # 选词
        sql = "select * from words order by usecount limit 1;"
        word_data = db.execute(sql).fetchone()
        new_word = word_data['word']
        sql = "update room set currentword = ? where id = ?;"
        db.execute(sql, (new_word, id))
        # 修改词语频率
        sql = "update words set usecount = usecount + 1 where id = ?;"
        db.execute(sql, (word_data['id'], ))
        
        # 如果已经有主持人了把主持人的状态换成2
        if room_info['hoster']:
            sql = "update roomplayer set drawingstatus = 2 where username = ?;"
            db.execute(sql, (room_info['hoster'], ))

        # 选一个新主持人
        sql = "select * from roomplayer where id = ?;"
        players = db.execute(sql, (id, )).fetchall()
        next_hoster = None
        for player in players:
            if player['drawingstatus'] == 0:
                next_hoster = player['username']
                break
        # 如果选不出来设置gamestatus为0，这里写个新函数，停止游戏状态需要修改很多东西
        if not next_hoster:
            init_game_table(id)
            isevent[0] = True
            return
        # 获取昵称
        sql = "select nickedname from user where username = ?;"
        nieckedname_data = db.execute(sql, (next_hoster, )).fetchone()
        send_system(id, f"{nieckedname_data['nickedname']}开始作画")

        # 设置新的主持人,roomplayer的drawingstatus和room中的hoster
        sql = "update room set hoster = ? where id = ?;"
        db.execute(sql, (next_hoster, id))
        sql = "update roomplayer set drawingstatus = 1 where username = ?;"
        db.execute(sql, (next_hoster,))

        # 重新设定词语时间，即targettime
        sql = "update room set targettime = ? where id = ?;"
        db.execute(sql, (int(time.time() + 120), id))

        # 总体一提交
        db.commit()

        isevent[0] = True

def check_game_status(id):
    '''
    检查游戏状态，如果都准备了就修改游戏状态
    id: 房间唯一标识id
    '''
    db = get_db()
    sql = "select isready from roomplayer where id = ?;"
    db_data = db.execute(sql, (id, )).fetchall()
    game_status = 1 if db_data else 0
    for item in db_data:
        game_status = game_status & item['isready']
    if len(db_data) >= 2 and game_status:
        # 修改游戏状态
        sql = "update room set gamestatus = 1 where id = ?;"
        db.execute(sql, (id, ))
        db.commit()
    elif game_status:
        init_game_table(id)
    
def init_game_table(id):
    '''
    id房间内游戏数据恢复到原始
    gamestatus:0
    currentword:NULL
    hoster:NULL
    targettime:NULL
    
    所有用户的
    isready:0
    drawingstatus:0
    grade:0
    '''
    db = get_db()
    sql = "update room set gamestatus = 0, currentword = NULL, hoster = NULL, targettime = NULL where id = ?;"
    db.execute(sql, (id, ))

    sql = "update roomplayer set isready = 0, drawingstatus = 0 where id = ?;"
    db.execute(sql, (id, ))

    db.commit()

def verify_room(id) -> bool:
    '''
    验证房间是否存在
    id:房间唯一标识
    '''
    db = get_db()

    sql = "select * from room where id = ?;"
    if not db.execute(sql, (id, )).fetchone():
        return False
    
    return True


# 同步画板数据
def update_board(id, username, boarddata, isevent):
    '''
    id: 房间唯一标识id
    '''
    db = get_db()

    sql = "select hoster from room where id = ?;"
    db_data = db.execute(sql, (id, )).fetchone()
    if db_data['hoster'] != username:
        return ('-1', '非绘画人')
    
    sql = "update room set boarddata = ? where id = ?;"
    db.execute(sql, (boarddata, id))
    db.commit()
    isevent[0] = True
    return ('0', '')

# 发送消息
def send_message(id, username, message, isevent):
    db = get_db()
    # 获取一下候选词和主持人，主持人的消息不参与比较
    sql = "select hoster, currentword from room where id = ?;"
    db_data = db.execute(sql, (id, )).fetchone()
    
    # 比较发送消息与候选词，成功则加分修改targettime
    if username != db_data['hoster'] and look_game_status(id):
        if message == db_data['currentword']:
            # 拿到这个username的nickedname
            sql = "select nickedname from user where username = ?;"
            user_data = db.execute(sql, (username, )).fetchone()
            send_system(id, f"{user_data['nickedname']}回答正确!")
            sql = "update roomplayer set grade = grade + 20 where username = ?;"
            db.execute(sql, (username, ))
            sql = "update room set targettime = ? where id = ?;"
            db.execute(sql, (int(time.time()), id))

    # 把消息加入
    sql = "insert into roommessage(tableid, username, message) values(?, ?, ?);"
    db.execute(sql, (id, username, message))

    # 统一提交一下
    db.commit()
    isevent[0] = True

def send_system(id, message):
    db = get_db()

    sql = "insert into roommessage(tableid, message, username) values(?, ?, ?);"
    db.execute(sql, (id, message, "admin"))

    db.commit()
